package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.JOptionPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableModel;
import Other.Glob;
import enums.SearchType;
public class DatabaseExtension {
	static DBInterface database;
	public static DBInterface getDatabase() {
		return database;
	}
	public static void setDatabase(DBInterface db){
		database = db;
	}
	/**
	 * My country methods
	 * @param result
	 * @return
	 */ 
	//Supler class methods
	private static NewTirDataBase[] returnTable(String[][] result) {
		NewTirDataBase table[] = new NewTirDataBase[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for(int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			database.printQuery(result);
			if (Glob.type ==  SearchType.COUNTRY){
				table[y] = new MyCountry(data);
				//System.out.println("returnTable has happened" + table);
			}else if (Glob.type ==  SearchType.PAYMENT){
				table[y] = new MyPayment(data);
			}else if (Glob.type ==  SearchType.INCOTERM){
				table[y] = new MyIncoterm(data);
			}else if (Glob.type ==  SearchType.TAX){
				table[y] = new MyTax(data);
			}else if (Glob.type ==  SearchType.ADDRESSEE){
				table[y] = new MyAddressee(data);
			}else if (Glob.type ==  SearchType.ADDRESSEECOUNTRY){
				table[y] = new MyCountry(data);
			}else if (Glob.type ==  SearchType.SUPPLIER){
				table[y] =  new MySupplier(data);
			}else if (Glob.type ==  SearchType.SUPPLIERCOUNTRY){
				table[y] = new MyCountry(data);
			}else if (Glob.type ==  SearchType.SUPPLIERPAYMENT){
				table[y] = new MyPayment(data);
			}else if (Glob.type ==  SearchType.SUPPLIERTAX){
				table[y] = new MyTax(data);
			}else if (Glob.type ==  SearchType.CLIENT){
				table[y] =  new MyClient(data);
			}else if (Glob.type ==  SearchType.CLIENTCOUNTRY){
				table[y] = new MyCountry(data);
			}else if (Glob.type ==  SearchType.CLIENTPAYMENT){
				table[y] = new MyPayment(data);
			}else if (Glob.type ==  SearchType.CLIENTTAX){
				table[y] = new MyTax(data);
			}else if (Glob.type ==  SearchType.DESTINATION){
				table[y] = new MyDestination(data);
			}else if (Glob.type ==  SearchType.DESTINATIONCOUNTRY){
				table[y] = new MyCountry(data);
			}else if (Glob.type ==  SearchType.SHIPMENT){
				table[y] = new MyShipment(data);
			}else if (Glob.type ==  SearchType.SHIPMENTCLIENT){
				table[y] = new MyClient(data);
			}else if (Glob.type ==  SearchType.SHIPMENTADDRESSEE){
				table[y] = new MyAddressee(data);
			}else if (Glob.type ==  SearchType.SHIPMENTDESTINATION){
				table[y] = new MyDestination(data);
			}else if (Glob.type ==  SearchType.SHIPMENTINCOTERM){
				table[y] = new MyIncoterm(data);
			}else if (Glob.type ==  SearchType.GOOD){
				table[y] = new MyGood(data);
			}else if (Glob.type ==  SearchType.EXPENDITURE){
				table[y] = new MyExpenditure(data);
			}else if (Glob.type ==  SearchType.TRANSPORT){
				table[y] = new MyTransport(data);
			}else if (Glob.type ==  SearchType.TRANSPORTDESTINATION){
				table[y] = new MyDestination(data);
			}else if (Glob.type ==  SearchType.TRANSPORTCOUNTRY){
				table[y] = new MyCountry(data);
			}else if (Glob.type ==  SearchType.TRANSPORTSUPPLIER){
				table[y] = new MySupplier(data);
			}else if (Glob.type ==  SearchType.ADDSHIPMENTTRANSPORT){
				table[y] = new MyTransport(data);
			}else if (Glob.type ==  SearchType.ADDSHIPMENTSHIPMENT){
				table[y] = new MyShipment(data);
			}
			//enter next condition right here
		}
		return table;
	}
	public static NewTirDataBase[] getTableID(String query) {
		//System.out.println("this are the query and the type respectively" + query);
		String[][] result = database.returnQuery(query);
		return returnTable(result);
	}
	public static int getRows(String query) {
		//System.out.println("this are the query and the type respectively" + query);
		String[][] result = database.returnQuery(query);
		int rows = result[0].length;
		return rows;
	}
	public static void deleteItem(String itemID) throws SQLException {
		database.connect();
		String delete;
		switch (Glob.type){
		case COUNTRY:
			delete = "delete from Country where CountryID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case PAYMENT:
			delete = "delete from Payment where PaymentID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case INCOTERM:
			delete = "delete from Incoterm where IncotermID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case TAX:
			delete = "delete from Tax where TaxID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case ADDRESSEE:
			delete = "delete from addressee where AddresseeID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case SUPPLIER:
			delete = "delete from supplier where supplierID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case CLIENT:
			delete = "delete from client where clientID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case DESTINATION:
			delete = "delete from destination where destinationID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
		case SHIPMENT:
			delete = "delete from shipment where shipmentID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
			
		case TRANSPORT:
			delete = "delete from transport where transportID='"
					+ itemID + "'";
			database.doSQL(delete);
			break;
			
			
			
			
			
			// enter new condition right here
		}
		database.close();
	}
	public static void deleteGood(String code, String shipmentID) throws SQLException {
		database.connect();
		String delete;
		delete = "delete from good where code ='"
				+ code + "' and shipmentId = '" +shipmentID+ "'";
		database.doSQL(delete);
		database.close();
	}
	public static void deleteExpenditure(String code, String shipmentID) throws SQLException {
		database.connect();
		String delete;
		delete = "delete from expenditure where code ='"
				+ code + "' and shipmentId = '" +shipmentID+ "'";
		database.doSQL(delete);
		database.close();
	}
	
	public static void deleteShipmentInTransport (String shipmentID) throws SQLException {
		
		
			database.connect();
			String delete;
			delete = "update shipment set transportID ="
					+null+ " where shipmentId = '" +shipmentID+ "'";
			database.doSQL(delete);
			database.close();
		
	}
	//Method to save or update a Mycountry object in the database
	public static boolean saveCountry(MyCountry country) throws SQLException{
		database.connect();
		if (country.getCountryID() != null) {
			country.setCountryID(country.getCountryID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] countries = getTableID("Select * from country");
		String id = "";
		for (NewTirDataBase i:countries){
			if (((MyCountry) i).getCountryID() != null) {
				((MyCountry) i).setCountryID(((MyCountry) i).getCountryID().replaceAll("\n", "").trim());
			} 
			//System.out.println("This is the for loop happening in saveCountry " + ((MyCountry) i).getCountryID());
			if(((MyCountry) i).getCountryID().equals(country.getCountryID())) {
				alreadyExists = true;
				id = ((MyCountry) i).getCountryID();
				//System.out.println("Esto son las IDs " + ((MyCountry) i).getCountryID());
			}
		}
		database.close();
	
		if (alreadyExists) {
			database.connect();
			database.doSQL("update country set name ='" + country.getCountryName() +"' where CountryID = '" + country.getCountryID() +"'");
			//System.out.println("Amending item");
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into country (CountryID, name) values ('" + country.getCountryID() +"','" + country.getCountryName() +"')";
			if (country.getCountryID()==null) {
				queryInsert = "insert into country (CountryID, name) values (" + country.getCountryID() +",'" + country.getCountryName() +"')";
			}
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	//method to save payment object in database table
	public static boolean savePayment(MyPayment payment) throws SQLException{
		database.connect();
		if (payment.getPaymentID() != null) {
			payment.setPaymentID(payment.getPaymentID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] payments = getTableID("Select * from payment");
		String id = "";
		for (NewTirDataBase i:payments){
			((MyPayment) i).setPaymentID(((MyPayment) i).getPaymentID().replaceAll("\n", "").trim());
			//System.out.println("This is the for loop happening in savePayment " + ((MyPayment) i).getPaymentID());
			if(((MyPayment) i).getPaymentID().equals(payment.getPaymentID())) {
				alreadyExists = true;
				id = ((MyPayment) i).getPaymentID();
				//System.out.println("Esto son las IDs " + ((MyPayment) i).getPaymentID());
			}
		}
		database.close();
		//System.out.println("This is the boolean in savePayment " + alreadyExists);
		if (alreadyExists) {
			database.connect();
			database.doSQL("update payment set name ='" + payment.getPaymentName() +"' where PaymentID = '" + payment.getPaymentID() +"'");
			//System.out.println("Amending item");
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into Payment (PaymentID, name) values ('" + payment.getPaymentID() +"','" + payment.getPaymentName() +"')";
			if (payment.getPaymentID()==null) {
				//System.out.println("Null SHit Found");
				queryInsert = "insert into payment (PaymentID, name) values (" + payment.getPaymentID() +",'" + payment.getPaymentName() +"')";
			}
			//System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	//method to save incoterm object in database table
	public static boolean saveIncoterm(MyIncoterm incoterm) throws SQLException{
		database.connect();
		if (incoterm.getIncotermID() != null) {
			incoterm.setIncotermID(incoterm.getIncotermID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] incoterms = getTableID("Select * from incoterm");
		String id = "";
		for (NewTirDataBase i:incoterms){
			((MyIncoterm) i).setIncotermID(((MyIncoterm) i).getIncotermID().replaceAll("\n", "").trim());
			//System.out.println("This is the for loop happening in saveincoterm " + ((Myincoterm) i).getIncotermID());
			if(((MyIncoterm) i).getIncotermID().equals(incoterm.getIncotermID())) {
				alreadyExists = true;
				id = ((MyIncoterm) i).getIncotermID();
				//System.out.println("Esto son las IDs " + ((Myincoterm) i).getIncotermID());
			}
		}
		database.close();
		//System.out.println("This is the boolean in saveincoterm " + alreadyExists);
		if (alreadyExists) {
			database.connect();
			database.doSQL("update incoterm set name ='" + incoterm.getIncotermName() +"' where incotermID = '" + incoterm.getIncotermID() +"'");
			//System.out.println("Amending item");
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into incoterm (incotermID, name) values ('" + incoterm.getIncotermID() +"','" + incoterm.getIncotermName() +"')";
			if (incoterm.getIncotermID()==null) {
				//System.out.println("Null SHit Found");
				queryInsert = "insert into incoterm (incotermID, name) values (" + incoterm.getIncotermID() +",'" + incoterm.getIncotermName() +"')";
			}
			//System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	//method to save tax object in database table
	public static boolean saveTax(MyTax tax) throws SQLException{
		database.connect();
		//System.out.println("This is the Taxid  happening in saveTax " + ((MyTax) tax).getTaxID() + ((MyTax) tax).getTaxName() + ((MyTax) tax).getType());
		if (tax.getTaxID() != null) {
			tax.setTaxID(tax.getTaxID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] taxes = getTableID("Select * from Tax");
		//System.out.println("This is the taxes variable  happening in saveTax " + taxes);
		String id = "";
		for (NewTirDataBase i:taxes){
			if (((MyTax) i).getTaxID() != null) {
				((MyTax) i).setTaxID(((MyTax) i).getTaxID().replaceAll("\n", "").trim());
			} 
			//System.out.println("This is the for loop happening in saveTax " + ((MyTax) i).getTaxID());
			if(((MyTax) i).getTaxID().equals(tax.getTaxID())) {
				alreadyExists = true;
				id = ((MyTax) i).getTaxID();
				//System.out.println("Esto son las IDs " + ((MyTax) i).getTaxID());
			}
		}
		database.close();
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update Tax set name ='" + tax.getTaxName() +"', tax = '"+ tax.getType() +"' where TaxID = '" + tax.getTaxID() +"'";
			database.doSQL(queryUpdate);
			//System.out.println("Amending item");
			database.close();
		}else{
			String taxID = tax.getTaxID();
			String taxName = tax.getTaxName();
			String taxType = tax.getType();
			if(taxID == null) {
				taxID = "null";
			}else {
				taxID = "'"+tax.getTaxID() +"'";
			}
			database.connect();
			String queryInsert = "insert into Tax (TaxID, name, tax) values ("+taxID+",'"+taxName+"','"+taxType+"')";
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	//method to save addresee object in database table
	public static boolean saveAddressee(MyAddressee addressee) throws SQLException{
		database.connect();
		if (addressee.getAddresseeID() != null) {
			addressee.setAddresseeID(addressee.getAddresseeID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] addressees = getTableID("select * from addressee");
		String id = "";
		for (NewTirDataBase i:addressees){
			if (((MyAddressee) i).getAddresseeID() != null) {
				((MyAddressee) i).setAddresseeID(((MyAddressee) i).getAddresseeID().replaceAll("\n", "").trim());
			} 
			if(((MyAddressee) i).getAddresseeID().equals(addressee.getAddresseeID())) {
				
				id = ((MyAddressee) i).getAddresseeID();
				alreadyExists = true;
			}
		}
		database.close();
		String countryID;
		String addresseeID;
		countryID = addressee.getCodeCountryID();
		addresseeID = addressee.getAddresseeID();
		if (alreadyExists) {
			if(countryID == null) {
				countryID = "null";
			}else {
				countryID = "'"+addressee.getCodeCountryID() +"'";
			}
			database.connect();
			String queryUpdate = "update addressee set addressee ='"
					+ addressee.getName() +"', address = '"
					+ addressee.getAddress()+"', postcode = '"
					+ addressee.getPostcode()+"', town = '"
					+ addressee.getTown()+"', province = '"
					+ addressee.getProvince()+"', countryID = "
					+ countryID+", phoneNumber = '"
					+ addressee.getPhoneNumber()+"', fax = '"
					+ addressee.getFax()+"', nif = '"
					+ addressee.getNif()+"', contact = '"
					+ addressee.getContact()+"', email = '"
					+ addressee.getEmail()+ "' where addresseeID = '"
					+ addressee.getAddresseeID() +"'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			countryID = addressee.getCodeCountryID();
			//System.out.println("This is the countryID inside the save method " + countryID);
			if(addresseeID == null) {
				addresseeID = "null";
			}else{
				addresseeID = "'"+addressee.getAddresseeID() +"'";
			}
			if(countryID == null) {
				countryID = "null";
			}else{
				countryID = "'"+addressee.getCodeCountryID() +"'";
			}
			database.connect();
			String queryInsert = "insert into addressee values ("+addresseeID+",'"
			+addressee.getName()+"','"
			+addressee.getAddress()+"','"
			+addressee.getPostcode()+"','"
			+addressee.getTown()+"','"
			+addressee.getProvince()+"',"
			+countryID+",'"
			+addressee.getPhoneNumber()+"','"
			+addressee.getFax()+"','"
			+addressee.getNif()+"','"
			+addressee.getContact()+"','"
			+addressee.getEmail()+"')";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	public static boolean saveSupplier(MySupplier supplier) throws SQLException{
		database.connect();
		//System.out.println("This is the addreseeID  happening in saveAddresee " + ((MyAddresee) supplier).getsupplierID() + ((MyAddresee) supplier).getName() + ((MyAddresee) supplier).getType());
		if (supplier.getSupplierID() != null) {
			supplier.setSupplierID(supplier.getSupplierID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		//NewTirDataBase[] suppliers = getTable("Select supplierID, supplier.name , address, postcode, town, province, supplier.countryID, country.name, phonenumber, fax, nif, contact, email from supplier, country where supplier.countryid = country.countryID");
		NewTirDataBase[] suppliers = getTableID("select * from supplier");
		//System.out.println("This is the taxes variable  happening in saveTax " + suppliers);
		for (NewTirDataBase s:suppliers){
			if (((MySupplier) s).getSupplierID() != null) {
				((MySupplier) s).setSupplierID(((MySupplier) s).getSupplierID().replaceAll("\n", "").trim());
			} 
			//System.out.println("This is the for loop happening in saveTax " + ((MySupplier) i).getsupplierID());
			if(((MySupplier) s).getSupplierID().equals(supplier.getSupplierID())) {
				alreadyExists = true;
				//System.out.println("Esto son las IDs " + ((MySupplier) i).getsupplierID());
			}
		}
		database.close();
		String supplierID = supplier.getSupplierID();
		if(supplierID == null) {
			supplierID = "null";
		}else {
			supplierID = "'"+supplier.getSupplierID() +"'";
		}
		String countryID = supplier.getCountryCodeID();
		if(countryID == null) {
			countryID = "null";
		}else {
			//System.out.println("the countryID equuals not null has happened in the save method");
			countryID = "'"+supplier.getCountryCodeID() +"'";
			//System.out.println("This is the countryID inside the save method if it is not null" + countryID);
		}
		String paymentID = supplier.getPaymentCodeID();
		if(paymentID == null) {
			paymentID = "null";
		}else {
			paymentID = "'"+supplier.getPaymentCodeID() +"'";
		}
		String taxID = supplier.getTaxCodeID();
		if(taxID == null) {
			taxID = "null";
		}else {
			taxID = "'"+supplier.getTaxCodeID() +"'";
		}
		//System.out.println("This is the boolean in saveTax " + alreadyExists);
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update supplier set supplier ='"
					+ supplier.getName() +"', address = '"
					+ supplier.getAddress()+"', postcode = '"
					+ supplier.getPostcode()+"', town = '"
					+ supplier.getTown()+"', province = '"
					+ supplier.getProvince()+"', maturity = '"
					+ supplier.getMaturity()+"', countryID = "
					+ countryID+", phoneNumber = '"
					+ supplier.getPhoneNumber()+"', fax = '"
					+ supplier.getFax()+"', nif = '"
					+ supplier.getNif()+"', paymentID = "
					+ paymentID+",taxID = "
					+ taxID+", email = '"
					+ supplier.getEmail()+ "' where supplierID = '"
					+ supplier.getSupplierID() +"'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into supplier values ("
					+supplierID+",'"
					+supplier.getName()+"','"
					+supplier.getAddress()+"','"
					+supplier.getPostcode()+"','"
					+supplier.getTown()+"','"
					+supplier.getProvince()+"','"
					+supplier.getMaturity()+"',"
					+countryID+",'"
					+supplier.getPhoneNumber()+"','"
					+supplier.getFax()+"','"
					+supplier.getNif()+"',"
					+paymentID+","
					+taxID+",'"
					+supplier.getEmail()+"')";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	public static boolean saveClient(MyClient client) throws SQLException{
		database.connect();
		//System.out.println("This is the addreseeID  happening in saveAddresee " + ((MyAddresee) Client).getClientID() + ((MyAddresee) Client).getName() + ((MyAddresee) Client).getType());
		if (client.getClientID() != null) {
			client.setClientID(client.getClientID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		//NewTirDataBase[] Clients = getTable("Select ClientID, Client.name , address, postcode, town, province, Client.countryID, country.name, phonenumber, fax, nif, contact, email from Client, country where Client.countryid = country.countryID");
		NewTirDataBase[] clients = getTableID("select * from client");
		//System.out.println("This is the taxes variable  happening in saveTax " + Clients);
		for (NewTirDataBase s:clients){
			if (((MyClient) s).getClientID() != null) {
				((MyClient) s).setClientID(((MyClient) s).getClientID().replaceAll("\n", "").trim());
			} 
			//System.out.println("This is the for loop happening in saveTax " + ((MyClient) i).getClientID());
			if(((MyClient) s).getClientID().equals(client.getClientID())) {
				alreadyExists = true;
				//System.out.println("Esto son las IDs " + ((MyClient) i).getClientID());
			}
		}
		database.close();
		String clientID = client.getClientID();
		if(clientID == null) {
			clientID = "null";
		}else {
			clientID = "'"+client.getClientID() +"'";
		}
		String countryID = client.getCountryCodeID();
		if(countryID == null) {
			countryID = "null";
		}else {
			//System.out.println("the countryID equuals not null has happened in the save method");
			countryID = "'"+client.getCountryCodeID() +"'";
			//System.out.println("This is the countryID inside the save method if it is not null" + countryID);
		}
		String paymentID = client.getPaymentCodeID();
		if(paymentID == null) {
			paymentID = "null";
		}else {
			paymentID = "'"+client.getPaymentCodeID() +"'";
		}
		String taxID = client.getTaxCodeID();
		if(taxID == null) {
			taxID = "null";
		}else{
			taxID = "'"+client.getTaxCodeID() +"'";
		}
		//System.out.println("This is the boolean in saveTax " + alreadyExists);
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update client set client ='"
					+ client.getName() +"', address = '"
					+ client.getAddress()+"', postcode = '"
					+ client.getPostcode()+"', town = '"
					+ client.getTown()+"', province = '"
					+ client.getProvince()+"', maturity = '"
					+ client.getMaturity()+"', comment1 = '"
					+ client.getComment1()+"', comment2 = '"
					+ client.getComment2()+"', countryID = "
					+ countryID+", phoneNumber = '"
					+ client.getPhoneNumber()+"', fax = '"
					+ client.getFax()+"', nif = '"
					+ client.getNif()+"', paymentID = "
					+ paymentID+",taxID = "
					+ taxID+", email = '"
					+ client.getEmail()+ "' where clientID = '"
					+ client.getClientID() +"'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into client values ("
					+clientID+",'"
					+client.getName()+"','"
					+client.getAddress()+"','"
					+client.getPostcode()+"','"
					+client.getTown()+"','"
					+client.getProvince()+"','"
					+client.getMaturity()+"','"
					+client.getComment1()+"','"
					+client.getComment2()+"',"
					+countryID+",'"
					+client.getPhoneNumber()+"','"
					+client.getFax()+"','"
					+client.getNif()+"',"
					+paymentID+","
					+taxID+",'"
					+client.getEmail()+"')";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	public static boolean saveDestination(MyDestination destination) throws SQLException{
		database.connect();
		//System.out.println("This is the DestinationID  happening in saveDestination " + ((MyDestination) Destination).getDestinationID() + ((MyDestination) Destination).getDestinationName());
		if (destination.getDestinationID() != null) {
			destination.setDestinationID(destination.getDestinationID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] destinations = getTableID("Select * from Destination");
		//System.out.println("This is the the glob type variable  happening in saveDestination " + Glob.type);
		//id = "";
		for (NewTirDataBase i:destinations){
			if (((MyDestination) i).getDestinationID() != null) {
				((MyDestination) i).setDestinationID(((MyDestination) i).getDestinationID().replaceAll("\n", "").trim());
			} 
			//System.out.println("This is the for loop happening in saveDestination " + ((MyDestination) i).getDestinationID());
			if(((MyDestination) i).getDestinationID().equals(destination.getDestinationID())) {
				alreadyExists = true;
			}
		}
		database.close();
		String destinationID = destination.getDestinationID();
		if(destinationID == null) {
			destinationID = "null";
		}else {
			destinationID = "'"+destination.getDestinationID()+"'";
		}
		String countryID = destination.getCodeCountryID();
		if(countryID == null) {
			countryID = "null";
		}else {
			//System.out.println("the countryID equuals not null has happened in the save method");
			countryID = "'"+destination.getCodeCountryID() +"'";
			//System.out.println("This is the countryID inside the save method if it is not null" + countryID);
		}
		//System.out.println("This is the boolean in saveDestination " + alreadyExists);
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update Destination set name ='"
					+ destination.getName() +"', countryID = "
					+ countryID +" where destinationID = '"		
					+ destination.getDestinationID() +"'";
			database.doSQL(queryUpdate);
			//System.out.println("Amending item");
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into Destination values ("
					+ destinationID +",'" 
					+ destination.getName() +"', "
					+ countryID + ")";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	public static boolean saveShipment(MyShipment shipment) throws SQLException{
		database.connect();
		//System.out.println("This is the addreseeID  happening in saveAddresee " + ((MyAddresee) Client).getClientID() + ((MyAddresee) Client).getName() + ((MyAddresee) Client).getType());
		if (shipment.getShipmentID() != null) {
			shipment.setShipmentID(shipment.getShipmentID().replaceAll("\n", "").trim());
		} else {
			System.out.println("null");
		}
		boolean alreadyExists = false;
		boolean insert = false;
		//NewTirDataBase[] Clients = getTable("Select ClientID, Client.name , address, postcode, town, province, Client.countryID, country.name, phonenumber, fax, nif, contact, email from Client, country where Client.countryid = country.countryID");
		NewTirDataBase[] shipments = getTableID("select * from shipment");
		//System.out.println("This is the taxes variable  happening in saveTax " + Clients);
		for (NewTirDataBase s:shipments){
			if (((MyShipment) s).getShipmentID() != null) {
				((MyShipment) s).setShipmentID(((MyShipment) s).getShipmentID().replaceAll("\n", "").trim());
			} 
			//System.out.println("This is the for loop happening in saveTax " + ((MyClient) i).getClientID());
			if(((MyShipment) s).getShipmentID().equals(shipment.getShipmentID())) {
				alreadyExists = true;
				//System.out.println("Esto son las IDs " + ((MyClient) i).getClientID());
			}
		}
		database.close();
		String shipmentID = shipment.getShipmentID();
		if(shipmentID == null) {
			shipmentID = "null";
		}else {
			shipmentID = "'"+shipment.getShipmentID() +"'";
		}
		String clientID = shipment.getClientCodeID();
		if(clientID == null) {
			clientID = "null";
		}else {
			clientID = "'"+shipment.getClientCodeID() +"'";
		}
		String addresseeID = shipment.getAddresseeCodeID();
		if(addresseeID == null) {
			addresseeID = "null";
		}else {
			//System.out.println("the countryID equuals not null has happened in the save method");
			addresseeID = "'"+shipment.getAddresseeCodeID() +"'";
			//System.out.println("This is the countryID inside the save method if it is not null" + countryID);
		}
		String destinationID = shipment.getDestinationCodeID();
		if(destinationID == null) {
			destinationID = "null";
		}else {
			destinationID = "'"+shipment.getDestinationCodeID() +"'";
		}
		String incotermID = shipment.getIncotermCodeID();
		if(incotermID == null) {
			incotermID = "null";
		}else {
			incotermID = "'"+shipment.getIncotermCodeID() +"'";
		}
		String rDate = shipment.getrDate();
		if(rDate == null) {
			rDate = "null";
		}else{
			rDate = "'"+shipment.getrDate()+"'";
		}
		String dDate = shipment.getdDate();
		if(dDate == null) {
			dDate = "null";
		}else{
			dDate = "'"+shipment.getdDate() +"'";
		}
		
		//System.out.println("This is the boolean in saveTax " + alreadyExists);
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update shipment set clientID ="
					+ clientID +", addresseeID = "
					+ addresseeID+", invoice = '"
					+ shipment.getInvoice()+"', rdate = "
					+ rDate+", destinationID = "
					+ destinationID+", ddate = "
					+ dDate+", currency = '"
					+ shipment.getCurrency()+"', kilos = '"
					+ shipment.getKilos()+"', volume = '"
					+ shipment.getVolume()+"', incotermID = "
					+ incotermID+", comment1 = '"
					+ shipment.getComment1() + "', comment2 = '" 
					+ shipment.getComment2() + "' where shipmentID = '"
					+ shipment.getShipmentID() +"'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into shipment values ("
					+null+","
					+clientID+","
					+addresseeID+",'"
					+shipment.getInvoice()+"',"
					+rDate+","
					+destinationID+","
					+dDate+",'"
					+shipment.getCurrency()+"','"
					+shipment.getKilos()+"','"
					+shipment.getVolume()+"',"
					+incotermID+",'"
					+shipment.getComment1()+"','" 
					+shipment.getComment2()+"',"
					+null+")";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
			insert = true;
		}
		return insert;
	}
	
	public static void insertShipment(String transportID, String shipmentID) throws SQLException{
		
		database.connect();
		String query = "update shipment set transportID = '"
				+transportID+ "' where shipmentID = '"
				+shipmentID+"'";
		System.out.println(query);
		database.doSQL(query);
		
		database.close();
		
		
	}
	public static boolean saveGood(MyGood good, String shipmentID) throws SQLException{
		database.connect();
		if (good.getCode() != null) {
			good.setCode(good.getCode().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] goods = getTableID("select * from good where ShipmentID = '"+shipmentID+"'");
		for (NewTirDataBase i:goods){
			if (((MyGood) i).getCode() != null) {
				((MyGood) i).setCode(((MyGood) i).getCode().replaceAll("\n", "").trim());
			} 
			if(((MyGood) i).getCode().equals(good.getCode())) {
				alreadyExists = true;
			}
		}
		database.close();
		String goodID = good.getGoodID();
		if(goodID == null) {
			goodID = "null";
		}else {
			goodID = "'"+good.getGoodID() +"'";
		}
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update good set description ='"
					+ good.getDescription() +"', measure = '"
					+ good.getMeasure()+"', unit = '"
					+ good.getUnit()+"', amount = '"
					+ good.getAmount()+"', parcel = '"
					+ good.getParcel()+"' where code = '"
					+ good.getCode() +"' and ShipmentID = '"
					+shipmentID+ "'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into good values ("
					+goodID+",'"
					+good.getCode()+"','"
					+good.getDescription()+"','"
					+good.getMeasure()+"','"
					+good.getUnit()+"','"
					+good.getAmount()+"','"
					+good.getParcel()+"', '"
					+good.getShipmentID()+"')";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	public static boolean saveExpenditure(MyExpenditure expenditure, String shipmentID) throws SQLException{
		database.connect();
		if (expenditure.getCode() != null) {
			expenditure.setCode(expenditure.getCode().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] expenditures = getTableID("select * from expenditure where ShipmentID = '"+shipmentID+"'");
		for (NewTirDataBase i:expenditures){
			if (((MyExpenditure) i).getCode() != null) {
				((MyExpenditure) i).setCode(((MyExpenditure) i).getCode().replaceAll("\n", "").trim());
			} 
			if(((MyExpenditure) i).getCode().equals(expenditure.getCode())) {
				alreadyExists = true;
			}
		}
		database.close();
		String expenditureID = expenditure.getExpenditureID();
		if(expenditureID == null) {
			expenditureID = "null";
		}else {
			expenditureID = "'"+expenditure.getExpenditureID() +"'";
		}
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update expenditure set description ='"
					+ expenditure.getDescription() +"', tax = '"
					+ expenditure.getTax()+"', amount = '"
					+ expenditure.getAmount()+"' where code = '"
					+ expenditure.getCode()+ "' and ShipmentID = '"
					+shipmentID+ "'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into expenditure values ("
					+expenditureID+",'"
					+expenditure.getCode()+"','"
					+expenditure.getDescription()+"','"
					+expenditure.getTax()+"','"
					+expenditure.getAmount()+"','"
					+expenditure.getShipmentID()+"')";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	
	public static boolean saveTransport(MyTransport transport) throws SQLException{
		database.connect();
		if (transport.getTransportID() != null) {
			transport.setTransportID(transport.getTransportID().replaceAll("\n", "").trim());
		} else {
			//System.out.println("null");
		}
		boolean alreadyExists = false;
		NewTirDataBase[] transports = getTableID("select * from transport");
		String id = "";
		for (NewTirDataBase i:transports){
			if (((MyTransport) i).getTransportID() != null) {
				((MyTransport) i).setTransportID(((MyTransport) i).getTransportID().replaceAll("\n", "").trim());
			} 
			if(((MyTransport) i).getTransportID().equals(transport.getTransportID())) {
				
				id = ((MyTransport) i).getTransportID();
				alreadyExists = true;
			}
		}
		database.close();
		String transportID;
		String countryID;
		String destinationID;
		String supplierID;
		countryID = transport.getCountryCode();
		transportID = transport.getTransportID();
		destinationID = transport.getDestinationCode();
		supplierID = transport.getSupplierCode();
		if(transportID == null) {
			transportID = "null";
		}else{
			transportID = "'"+transport.getTransportID() +"'";
		}
		if(countryID == null) {
			countryID = "null";
		}else{
			countryID = "'"+transport.getCountryCode() +"'";
		}
		if(destinationID == null) {
			destinationID = "null";
		}else{
			destinationID = "'"+transport.getDestinationCode() +"'";
		}if(supplierID == null) {
			supplierID = "null";
		}else{
			supplierID = "'"+transport.getSupplierCode() +"'";
		}
		if (alreadyExists) {
			database.connect();
			String queryUpdate = "update transport set lorry ='"
					+ transport.getLorry() +"', departure = '"
					+ transport.getDeparture()+"', agent = '"
					+ transport.getAgent()+"', destinationID = "
					+ destinationID+", supplierID = "
					+ supplierID+", countryID = "
					+ countryID+", comments = '"
					+ transport.getComments()+"' where transportID = '"
					+ transport.getTransportID()+"'";
			database.doSQL(queryUpdate);
			System.out.println(queryUpdate);
			database.close();
		}else{
			database.connect();
			String queryInsert = "insert into transport values ("+transportID+",'"
			+transport.getLorry()+"','"
			+transport.getDeparture()+"','"
			+transport.getAgent()+"',"
			+destinationID+","
			+supplierID+","
			+countryID+", '"
			+transport.getComments()+"')";
			System.out.println(queryInsert);
			database.doSQL(queryInsert);	
			database.close();
		}
		return alreadyExists;
	}
	public static Integer getLastColumnId(String query) {
		ArrayList <Integer> list =  columnIDs(query);
		int columnCount;
		if(list.isEmpty()){
			columnCount= 0;
		}else {
			columnCount = list.get(list.size()-1);
		}
		System.out.println("This is the result of columns inside getLastColumnID " + columnCount);
		return columnCount;
	}
	//method that returns the all the column ids on a table
	public static  ArrayList <Integer> columnIDs(String query) {
		ArrayList <Integer>ids = new ArrayList<Integer>();
		Vector<String> columnNames = null;
		//Vector<Vector<Object>> data = null;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			//connect with the database
			Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/newtir", "root", "");
			// The Connection is obtained
			Statement stmt = (Statement) con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			//System.out.println("this is the string got from search Bar " + query);
			ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();
			// names of columns
			columnNames = new Vector<String>();
			int columnCount = metaData.getColumnCount();
			for (int column = 1; column <= columnCount; column++) {
				columnNames.add(metaData.getColumnName(column));
			}
			// data of the table
			//data = new Vector<Vector<Object>>();
			while (rs.next()) {
				Vector<Object> vector = new Vector<Object>();
				for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
					Object ss  =rs.getObject(columnIndex);
					vector.add(ss);
					if (columnIndex==1){
						ids.add(Integer.valueOf(ss.toString()));
					}
				}
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ids;
	}
	
	//method that returns the all the column ids on a table
	public static  ArrayList <String> columnIDsString(String query) {
		ArrayList <String>ids = new ArrayList<String>();
		Vector<String> columnNames = null;
		//Vector<Vector<Object>> data = null;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			//connect with the database
			Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/newtir", "root", "");
			// The Connection is obtained
			Statement stmt = (Statement) con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			//System.out.println("this is the string got from search Bar " + query);
			ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();
			// names of columns
			columnNames = new Vector<String>();
			int columnCount = metaData.getColumnCount();
			for (int column = 1; column <= columnCount; column++) {
				columnNames.add(metaData.getColumnName(column));
			}
			// data of the table
			//data = new Vector<Vector<Object>>();
			while (rs.next()) {
				Vector<Object> vector = new Vector<Object>();
				for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
					Object ss  =rs.getObject(columnIndex);
					vector.add(ss);
					if (columnIndex==1){
						ids.add(ss.toString());
					}
				}
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ids;
	}
	public static String[][] printQueryArea(String query, JTextArea area){
		String[][] s = database.returnQuery(query);
		for (int y = 0; y < s[0].length; y++) {
			for (int x = 0; x < s.length; x++) {
				area.append(s[x][y] + "\t");
			}
		}
		return null;
	}
	
	public  static  DefaultTableModel buildTableModel(String query) {
		//variable that holds the ids of the 
		ArrayList<Integer> ids;
		ids = new ArrayList<Integer>();
		Vector<String> columnNames = null;
		Vector<Vector<Object>> data = null;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			//connect with the database
			Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/newtir", "root", "");
			// The Connection is obtained
			Statement stmt = (Statement) con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			// System.out.println("this is the string got from search Bar " + s);
			ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();
			// names of columns
			columnNames = new Vector<String>();
			int columnCount = metaData.getColumnCount();
			for (int column = 1; column <= columnCount; column++) {
				columnNames.add(metaData.getColumnName(column));
			}
			// data of the table
			data = new Vector<Vector<Object>>();
			while (rs.next()) {
				Vector<Object> vector = new Vector<Object>();
				for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
					Object ss  =rs.getObject(columnIndex);
					vector.add(ss);
					//System.out.println("Iterating");
					if (columnIndex==1){
						//System.out.println("This is the buidlTAblemethod adding column indexes to an array" + ss);
						ids.add(Integer.valueOf(ss.toString()));
					}
				}
				data.add(vector);
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new DefaultTableModel(data, columnNames);
	}
	
	
}
